package br.com.banco.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import br.com.banco.control.Login;
import br.com.banco.model.Cliente;
import br.com.banco.model.Conta;
import br.com.banco.model.Usuario;

public class UsuarioDAO {

	Connection conexao;

	private void obterConexao() throws Exception {
		BancoDBManager conexaoBanco = new BancoDBManager();
		// this.conexao = conexaoBanco.obterConexaoMySQL();
        this.conexao = conexaoBanco.obterConexaoJavaDb();
	}

	private void fecharConexao() throws Exception {
		this.conexao.close();
	}

	public Usuario retreive(int id) {

		Usuario usuario = null;
		try {

			obterConexao();

			String sql = "SELECT id, nivel, nome FROM usuario WHERE id = ? ";
			PreparedStatement stmt = conexao.prepareStatement(sql);
			stmt.setInt(1, id);

			ResultSet rs = stmt.executeQuery();
			if (rs.next()) {
				usuario = new Usuario();
				usuario.setId(rs.getInt("id"));
				usuario.setNome(rs.getString("nome"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				fecharConexao();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return usuario;

	}

	public Login retreiveFuncionario(Login log) {

		try {
			obterConexao();

			String sql = "SELECT id, login, senha FROM login WHERE login = ? AND senha = ? ";
			PreparedStatement stmt = conexao.prepareStatement(sql);

			ResultSet rs = stmt.executeQuery();

			if (rs.next()) {
			} else {
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				fecharConexao();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return log;

	}

	public Integer cadastrarUsuario(Usuario usuar) {

		try {
			obterConexao();

			String sql = "INSERT INTO usuario (nivel,nome) VALUES (?,?) ";
			PreparedStatement stmt = conexao.prepareStatement(sql);
			stmt.setString(2, usuar.getNome());
			stmt.execute();

			String sql1 = "SELECT id FROM usuario WHERE nivel = ? AND nome = ? ";
			PreparedStatement stmt1 = conexao.prepareStatement(sql1);
			stmt1 = conexao.prepareStatement(sql1);
			stmt1.setString(2, usuar.getNome());
			ResultSet rs = stmt1.executeQuery();
			rs.next();
			Integer codigo = rs.getInt("id");

			String sql2 = "INSERT INTO login (id,login,senha) VALUES (?,?,?) ";
			PreparedStatement stmt2 = conexao.prepareStatement(sql2);
			stmt2.setInt(1, codigo);
			stmt2.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				fecharConexao();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return 0;

	}

	public boolean createCliente(Usuario cliente, Conta conta) {
		int count = 0;
		try {

			obterConexao();

			String sql = "INSERT INTO usuario (nivel,nome) VALUES (?,?) ";
			PreparedStatement stmt = conexao.prepareStatement(sql);
			stmt.setString(2, cliente.getNome());
			stmt.execute();

			String sql1 = "SELECT id FROM usuario WHERE nivel = ? AND nome = ? ";
			PreparedStatement stmt1 = conexao.prepareStatement(sql1);
			stmt1 = conexao.prepareStatement(sql1);
			stmt1.setString(2, cliente.getNome());
			ResultSet rs = stmt1.executeQuery();
			rs.next();
			Integer codigo = rs.getInt("id");

			String sql2 = "INSERT INTO logincliente (id,senha,frase) VALUES (?,?,?) ";
			PreparedStatement stmt2 = conexao.prepareStatement(sql2);
			stmt2.setInt(1, codigo);
			count = stmt2.executeUpdate();

			conta.setCliente(new Cliente());
			conta.setSaldo(Double.parseDouble("0"));
			if (count > 0) {
				obterConexao();
				String sql4 = "INSERT INTO conta (agencia,conta,digito,saldo,idcliente) VALUES (?,?,?,?,?) ";
				PreparedStatement stmt4 = conexao.prepareStatement(sql4);
				stmt4.setInt(1, conta.getAgencia());
				stmt4.setInt(2, conta.getNumero());
				stmt4.setDouble(4, conta.getSaldo());
				stmt4.setInt(5, conta.getCliente().getId());
				count += stmt4.executeUpdate();
			}

		} catch (NumberFormatException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				fecharConexao();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return count > 0;
	}

}
